Imports ADOConnection
Imports System.Data
Imports System.Data.SqlClient

Public Class CThuoc
    Private m_MaThuoc As String
    Private m_TenThuoc As String
    Private m_MaLoaiThuoc As String
    Private m_DonViTinh As String

#Region "Property"
    Property MaThuoc() As String
        Get
            Return m_MaThuoc
        End Get
        Set(ByVal value As String)
            m_MaThuoc = value
        End Set
    End Property

    Property TenThuoc() As String
        Get
            Return m_TenThuoc
        End Get
        Set(ByVal value As String)
            m_TenThuoc = value
        End Set
    End Property

    Property MaLoaiThuoc() As String
        Get
            Return m_MaLoaiThuoc
        End Get
        Set(ByVal value As String)
            m_MaLoaiThuoc = value
        End Set
    End Property

    Property DonViTinh() As String
        Get
            Return m_DonViTinh
        End Get
        Set(ByVal value As String)
            m_DonViTinh = value
        End Set
    End Property

#End Region

End Class

Public Class CThuocs
    Public Shared Function GetThuocListByLoaiChaID(ByVal loaichaID As String) As ArrayList
        Dim aryItems As New ArrayList

        Dim strSQL As String
        Dim objConnection As IADOConnection = g_objConnFactory.GetConnection
        Dim objUtility As IDBUtility = objConnection.DBUtility

        strSQL = "SELECT mathuoc, tenthuoc, maloaithuoc, donvitinh" & _
                " from tblDMTHUOC" & _
                " where maloaithuoc = '" & loaichaID & "'" & _
                "  order by mathuoc"
        Dim objReader As CDataReader = Nothing
        Dim objCommand As IDbCommand = objConnection.CreateCommand(strSQL)
        Try
            objReader = objConnection.GetDataReader(objCommand, True)

            While objReader.Read
                Dim objItem As New CThuoc
                With objItem
                    .MaThuoc = objReader.GetString(0)
                    .TenThuoc = objReader.GetString(1)
                    .MaLoaiThuoc = objReader.GetString(2)
                    .DonViTinh = objReader.GetString(3)
                End With
                aryItems.Add(objItem)
            End While

        Catch ex As System.Exception
            MsgBox(strSQL)
            'Throw ex
        Finally
            If Not IsNothing(objReader) Then
                objReader.Close()
            End If
            objConnection.Close()
        End Try

        Return aryItems

    End Function

    Public Shared Function GetThuocListByTen(ByVal tenthuoc As String) As ArrayList
        Dim aryItems As New ArrayList

        Dim strSQL As String
        Dim objConnection As IADOConnection = g_objConnFactory.GetConnection
        Dim objUtility As IDBUtility = objConnection.DBUtility

        strSQL = "SELECT mathuoc, tenthuoc, maloaithuoc, donvitinh" & _
                " from tblDMTHUOC" & _
                " where tenthuoc like N'" & tenthuoc & "%'" & _
                "  order by tenthuoc"
        Dim objReader As CDataReader = Nothing
        Dim objCommand As IDbCommand = objConnection.CreateCommand(strSQL)
        Try
            objReader = objConnection.GetDataReader(objCommand, True)
            While objReader.Read
                Dim objItem As New CThuoc
                With objItem
                    .MaThuoc = objReader.GetString(0)
                    .TenThuoc = objReader.GetString(1)
                    .MaLoaiThuoc = objReader.GetString(2)
                    .DonViTinh = objReader.GetString(3)
                End With
                aryItems.Add(objItem)
            End While

        Catch ex As System.Exception
            MsgBox(strSQL)
            'Throw ex
        Finally
            If Not IsNothing(objReader) Then
                objReader.Close()
            End If
            objConnection.Close()
        End Try

        Return aryItems

    End Function

    Public Shared Function GetThuocByTenandDVT(ByVal tenthuoc As String, ByVal DVT As String) As CThuoc
        Dim strSQL As String
        Dim objConnection As IADOConnection = g_objConnFactory.GetConnection
        Dim objUtility As IDBUtility = objConnection.DBUtility

        strSQL = "SELECT mathuoc, tenthuoc, maloaithuoc, donvitinh" & _
                " from tblDMTHUOC" & _
                " where tenthuoc like N'" & tenthuoc & "' and donvitinh = N'" & DVT & "'"

        Dim objReader As CDataReader = Nothing
        Dim objCommand As IDbCommand = objConnection.CreateCommand(strSQL)
        Dim objItem As New CThuoc
        Try
            objReader = objConnection.GetDataReader(objCommand, True)
            While objReader.Read
                With objItem
                    .MaThuoc = objReader.GetString(0)
                    .TenThuoc = objReader.GetString(1)
                    .MaLoaiThuoc = objReader.GetString(2)
                    .DonViTinh = objReader.GetString(3)
                End With
            End While

        Catch ex As System.Exception
            MsgBox(strSQL)
            'Throw ex
        Finally
            If Not IsNothing(objReader) Then
                objReader.Close()
            End If
            objConnection.Close()
        End Try

        Return objItem

    End Function

    Public Shared Function GetThuocByMa(ByVal mathuoc As String) As CThuoc
        Dim strSQL As String
        Dim objConnection As IADOConnection = g_objConnFactory.GetConnection
        Dim objUtility As IDBUtility = objConnection.DBUtility

        strSQL = "SELECT mathuoc, tenthuoc, maloaithuoc, donvitinh" & _
                " from tblDMTHUOC" & _
                " where mathuoc = '" & mathuoc & "'"

        Dim objReader As CDataReader = Nothing
        Dim objCommand As IDbCommand = objConnection.CreateCommand(strSQL)
        Dim objItem As New CThuoc
        Try
            objReader = objConnection.GetDataReader(objCommand, True)
            While objReader.Read
                With objItem
                    .MaThuoc = objReader.GetString(0)
                    .TenThuoc = objReader.GetString(1)
                    .MaLoaiThuoc = objReader.GetString(2)
                    .DonViTinh = objReader.GetString(3)
                End With
            End While

        Catch ex As System.Exception
            MsgBox(strSQL)
            'Throw ex
        Finally
            If Not IsNothing(objReader) Then
                objReader.Close()
            End If
            objConnection.Close()
        End Try

        Return objItem

    End Function

    Public Shared Function GetThuocByTen(ByVal tenthuoc As String) As CThuoc
        Dim strSQL As String
        Dim objConnection As IADOConnection = g_objConnFactory.GetConnection
        Dim objUtility As IDBUtility = objConnection.DBUtility

        strSQL = "SELECT mathuoc, tenthuoc, maloaithuoc, donvitinh" & _
                " from tblDMTHUOC" & _
                " where tenthuoc like N'" & tenthuoc & "'"

        Dim objReader As CDataReader = Nothing
        Dim objCommand As IDbCommand = objConnection.CreateCommand(strSQL)
        Dim objItem As New CThuoc
        Try
            objReader = objConnection.GetDataReader(objCommand, True)
            While objReader.Read
                With objItem
                    .MaThuoc = objReader.GetString(0)
                    .TenThuoc = objReader.GetString(1)
                    .MaLoaiThuoc = objReader.GetString(2)
                    .DonViTinh = objReader.GetString(3)
                End With
            End While

        Catch ex As System.Exception
            MsgBox(strSQL)
            'Throw ex
        Finally
            If Not IsNothing(objReader) Then
                objReader.Close()
            End If
            objConnection.Close()
        End Try

        Return objItem

    End Function

    Public Shared Function GetNewCode(ByVal mathuoc As String) As String
        Dim value As String
        Dim cmd As SqlCommand = Nothing
        Try
            Dim par(1) As SqlClient.SqlParameter
            par(0) = New SqlParameter("@mathuoc", SqlDbType.NVarChar, 14)
            par(0).Value = mathuoc
            par(1) = New SqlParameter("@newcode", SqlDbType.NVarChar, 4)
            par(1).Direction = ParameterDirection.Output
            cmd = BaseDB.ExecSqlProcedure_Command("sp_GetNewCode", par)
            value = cmd.Parameters("@newcode").Value
        Catch ex As Exception
            Throw ex
        Finally
            cmd.Connection.Close()
        End Try
        Return value
    End Function

    Public Shared Function InsertThuoc(ByVal objThuoc As CThuoc) As Long
        Dim objConnection As IADOConnection = g_objConnFactory.GetConnection
        Dim objDBUtility As IDBUtility = objConnection.DBUtility
        'mathuoc, tenthuoc,maloaithuoc,donvitinh,ghichu

        'Dim sInsert As String = objStringBuilder.ToString
        Dim sInsert As String = "INSERT INTO tblDMTHUOC" & _
        " (mathuoc,tenthuoc,maloaithuoc,donvitinh) VALUES(" & _
        objDBUtility.GetParamPlaceHolder("MaThuoc", True) & _
        objDBUtility.GetParamPlaceHolder("TenThuoc", True) & _
        objDBUtility.GetParamPlaceHolder("MaLoaiThuoc", True) & _
        objDBUtility.GetParamPlaceHolder("DonViTinh", False) & _
        ")"

        Dim objCommand As IDbCommand = objConnection.CreateCommand(sInsert)
        Dim objTransaction As IDbTransaction = objConnection.BeginTransaction
        Dim lRowsAffected As Long = 0

        Try
            With objCommand
                .Transaction = objTransaction
                .Parameters.Add(objDBUtility.CreateParameter("MaThuoc", DbType.String, objThuoc.MaThuoc, objThuoc.MaThuoc.Length))
                .Parameters.Add(objDBUtility.CreateParameter("TenThuoc", DbType.String, objThuoc.TenThuoc, objThuoc.TenThuoc.Length))
                .Parameters.Add(objDBUtility.CreateParameter("MaLoaiThuoc", DbType.String, objThuoc.MaLoaiThuoc, objThuoc.MaLoaiThuoc.Length))
                .Parameters.Add(objDBUtility.CreateParameter("DonViTinh", DbType.String, objThuoc.DonViTinh, objThuoc.DonViTinh.Length))
                'Call objDBUtility.Prepare(objCommand)
                lRowsAffected = .ExecuteNonQuery()

            End With

            'lSerialKey = objDBUtility.InsertAndReturnSerialKey(objCommand, "DoiTuong_ID", "DoiTuong")
            objTransaction.Commit()

        Catch ex As System.Exception
            objTransaction.Rollback()
            'Throw ex
        Finally
            objConnection.Close()
        End Try

        Return lRowsAffected
    End Function

    Public Shared Function UpdateThuoc(ByVal objThuoc As CThuoc) As Long
        Dim objConnection As IADOConnection = g_objConnFactory.GetConnection
        Dim objDBUtility As IDBUtility = objConnection.DBUtility

        'Dim sInsert As String = objStringBuilder.ToString
        Dim sUpdate As String = "UPDATE tblDMTHUOC SET tenthuoc = "
        sUpdate &= objDBUtility.GetParamPlaceHolder("TenThuoc", True)
        sUpdate &= " maloaithuoc = "
        sUpdate &= objDBUtility.GetParamPlaceHolder("MaLoaiThuoc", True)
        sUpdate &= " donvitinh = "
        sUpdate &= objDBUtility.GetParamPlaceHolder("DonViTinh", False)
        sUpdate &= " WHERE mathuoc = " & _
        objDBUtility.GetParamPlaceHolder("MaThuoc", False)

        Dim objCommand As IDbCommand = objConnection.CreateCommand(sUpdate)
        Dim objTransaction As IDbTransaction = objConnection.BeginTransaction
        Dim lRowsAffected As Long = 0

        Try
            With objCommand
                .Transaction = objTransaction
                .Parameters.Add(objDBUtility.CreateParameter("MaThuoc", DbType.String, objThuoc.MaThuoc, objThuoc.MaThuoc.Length))
                .Parameters.Add(objDBUtility.CreateParameter("TenThuoc", DbType.String, objThuoc.TenThuoc, objThuoc.TenThuoc.Length))
                .Parameters.Add(objDBUtility.CreateParameter("MaLoaiThuoc", DbType.String, objThuoc.MaLoaiThuoc, objThuoc.MaLoaiThuoc.Length))
                .Parameters.Add(objDBUtility.CreateParameter("DonViTinh", DbType.String, objThuoc.DonViTinh, objThuoc.DonViTinh.Length))
                'Call objDBUtility.Prepare(objCommand)
                lRowsAffected = .ExecuteNonQuery()

            End With

            'lSerialKey = objDBUtility.InsertAndReturnSerialKey(objCommand, "DoiTuong_ID", "DoiTuong")
            objTransaction.Commit()

        Catch ex As System.Exception
            objTransaction.Rollback()
            'Throw ex
        Finally
            objConnection.Close()
        End Try

        Return lRowsAffected
    End Function

    Public Shared Function DeleteThuoc(ByVal objThuoc As CThuoc) As Long
        Dim objConnection As IADOConnection = g_objConnFactory.GetConnection
        Dim objDBUtility As IDBUtility = objConnection.DBUtility

        Dim sDelete As String = "DELETE FROM tblDMTHUOC WHERE (mathuoc = "
        sDelete &= objDBUtility.GetParamPlaceHolder("MaThuoc", False)
        sDelete &= ")"

        Dim objCommand As IDbCommand = objConnection.CreateCommand(sDelete)
        Dim objTransaction As IDbTransaction = objConnection.BeginTransaction
        Dim lRowsAffected As Long = 0

        Try
            With objCommand
                .Transaction = objTransaction
                .Parameters.Add(objDBUtility.CreateParameter("MaThuoc", DbType.String, objThuoc.MaThuoc, objThuoc.MaThuoc.Length))
                'Call objDBUtility.Prepare(objCommand)
                lRowsAffected = .ExecuteNonQuery()

            End With

            'lSerialKey = objDBUtility.InsertAndReturnSerialKey(objCommand, "DoiTuong_ID", "DoiTuong")
            objTransaction.Commit()

        Catch ex As System.Exception
            objTransaction.Rollback()
            'Throw ex
        Finally
            objConnection.Close()
        End Try

        Return lRowsAffected
    End Function

    Public Shared Function UpdateThuoc(ByVal objThuoc As CThuoc, ByVal mathuoc As String) As Long
        Dim objConnection As IADOConnection = g_objConnFactory.GetConnection
        Dim objDBUtility As IDBUtility = objConnection.DBUtility

        'Dim sInsert As String = objStringBuilder.ToString
        Dim sUpdate As String = "UPDATE tblDMTHUOC SET tenthuoc = "
        sUpdate &= objDBUtility.GetParamPlaceHolder("TenThuoc", True)
        sUpdate &= " mathuoc = "
        sUpdate &= objDBUtility.GetParamPlaceHolder("MaThuocMoi", True)
        sUpdate &= " maloaithuoc = "
        sUpdate &= objDBUtility.GetParamPlaceHolder("MaLoaiThuoc", True)
        sUpdate &= " donvitinh = "
        sUpdate &= objDBUtility.GetParamPlaceHolder("DonViTinh", False)
        sUpdate &= " WHERE mathuoc = " & _
        objDBUtility.GetParamPlaceHolder("MaThuocCu", False)

        Dim objCommand As IDbCommand = objConnection.CreateCommand(sUpdate)
        Dim objTransaction As IDbTransaction = objConnection.BeginTransaction
        Dim lRowsAffected As Long = 0

        Try
            With objCommand
                .Transaction = objTransaction
                .Parameters.Add(objDBUtility.CreateParameter("MaThuocCu", DbType.String, mathuoc, mathuoc.Length))
                .Parameters.Add(objDBUtility.CreateParameter("MaThuocMoi", DbType.String, objThuoc.MaThuoc, objThuoc.MaThuoc.Length))
                .Parameters.Add(objDBUtility.CreateParameter("TenThuoc", DbType.String, objThuoc.TenThuoc, objThuoc.TenThuoc.Length))
                .Parameters.Add(objDBUtility.CreateParameter("MaLoaiThuoc", DbType.String, objThuoc.MaLoaiThuoc, objThuoc.MaLoaiThuoc.Length))
                .Parameters.Add(objDBUtility.CreateParameter("DonViTinh", DbType.String, objThuoc.DonViTinh, objThuoc.DonViTinh.Length))
                'Call objDBUtility.Prepare(objCommand)
                lRowsAffected = .ExecuteNonQuery()

            End With

            'lSerialKey = objDBUtility.InsertAndReturnSerialKey(objCommand, "DoiTuong_ID", "DoiTuong")
            objTransaction.Commit()

        Catch ex As System.Exception
            objTransaction.Rollback()
            'Throw ex
        Finally
            objConnection.Close()
        End Try

        Return lRowsAffected
    End Function
End Class
